package gbench.sandbox;

import org.junit.jupiter.api.Test;

import java.util.*;
import java.util.stream.*;

import gbench.common.fs.XlsFile.SimpleExcel;
import gbench.common.fs.XlsFile.StrMatrix;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.SQL;
import gbench.commonApp.data.DataApp;

import static gbench.common.tree.LittleTree.*;
import gbench.common.tree.LittleTree.Term;

/**
 * 
 * @author gbench
 *
 */
public class JunitPrepareData {
	/**
	 * 
	 * 
	 * @author gbench
	 *
	 */
	public class MyDataApp extends DataApp {

		/**
		 * 
		 * @param name 数据库名称
		 */
		public MyDataApp(String dbname) {
			this.dbname = dbname;
			this.reload(this.configureDbName(dbname));
		}

		/**
		 * 数据查询
		 * 
		 * @param sql sql 语句
		 * @return IRecord 集合
		 */
		public List<IRecord> sqlquery(final String sql, String dbname) {
			reload(this.configureDbName(dbname));
			return this.jdbc.sql2records(sql);
		}

		/**
		 * 数据查询
		 * 
		 * @param sql sql 语句
		 * @return IRecord 集合
		 */
		public List<IRecord> sqlquery(final String sql) {
			return this.sqlquery(sql, dbname);
		}

		/**
		 * 配置数据库
		 * 
		 * @param dbname
		 * @return 设定数据库
		 */
		public IRecord configureDbName(final String dbname) {
			return cfg.derive("url", MFT("jdbc:mysql://localhost:3306/{0}?serverTimezone=GMT%2B8", dbname));
		}

		private String dbname; // 数据库名称

	}

	/**
	 * 
	 * @param objects
	 */
	public static void println(final Object... objects) {
		System.out.println(Arrays.asList(objects));
	}

	/**
	 * 
	 */
	@Test
	public Map<String, StrMatrix> read_excel() {
		final var demodb = "E:/slicee/ws/项目/平安银行项目/data/demodb.xlsx";
		final var excel = SimpleExcel.of(demodb);
		final var n = excel.sheets().size();
		final Map<String, StrMatrix> lhm = new LinkedHashMap<String, StrMatrix>();
		for (int i = 0; i < n; i++) {
			final var name = excel.shtid2sheetname(i);
			final var mx = excel.autoDetect(i);
			lhm.put(name, mx);
		}
		return lhm;
	}

	@Test
	public void foo() {
		final var db = "demodb1";
		final var dataApp = new MyDataApp(db);// 打开一个数据库 db1,db1 需要在创建
		read_excel().forEach((name, mx) -> {
			final var keys = mx.header();
			final var builder = IRecord.builder(keys);
			final var rec = builder.get(mx.row(1));
			final var database = dataApp.getDataBase(); // 获取数据库对象
			final var tableName = Term.FT("T_$0", name);// 默认表名
			final var namedsql = new SQL(tableName, rec);
			final var create_tbl_sql = namedsql.createTable().get(2);
			
			System.out.println("创建数据表  sql:");
			System.out.println(create_tbl_sql);
			System.out.println("\n分录明细:");
			
			dataApp.withTransaction(sess -> {
				if (database.exists(tableName))
					sess.sqlexecute(Term.FT("drop table $0", tableName));
				sess.sqlexecute(create_tbl_sql);
				final List<String> sqls = mx.rowS().map(builder::get).map(e -> new SQL(tableName, e).insert())
						.collect(Collectors.toList());
				for (var s : sqls)
					sess.sqlexecute(s);
				final var recs = sess.sql2records(Term.FT("select * from $0", tableName));
				recs.forEach(e -> {					System.out.println(e);
				});
			});

		});

	}
}